package org.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.common.dto.ExcelDto;
import org.common.exception.MetadataException;

public class ExcelUtil {
	private static final String XLSX_FILE_NAME = ".xlsx";
	private static final String XLTX_FILE_NAME = ".xltx";
	private static final String XLS_FILE_NAME = ".xls";

	/**
	 * 将excel转成exceldto对象
	 * 
	 * @param excelPath
	 * @param sheetName
	 * @throws Exception
	 */
	public static ExcelDto initExceldto(String excelPath, String sheetName) throws Exception {
		File file = new File(excelPath);

		Workbook wb = null;
		try {
			wb = ExcelUtil.createWorkbook(file);
		} catch (Exception e) {
			throw new MetadataException("创建workbook发生异常", e);
		}

		Sheet sheet = wb.getSheet(sheetName);
		List<String> title = createTitle(sheet);
		List<List<Object>> data = createData(sheet);

		wb.close();
		return new ExcelDto(title, data, excelPath);
	}

	public static List<ExcelDto> initExcelDtoList(String excelPath) throws MetadataException {
		File file = new File(excelPath);

		Workbook wb = null;
		try {
			wb = ExcelUtil.createWorkbook(file);
		} catch (Exception e) {
			throw new MetadataException("创建workbook发生异常", e);
		}

		List<ExcelDto> excelDtos = new ArrayList<ExcelDto>();
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			Sheet sheet = wb.getSheetAt(i);
			List<String> title = createTitle(sheet);
			List<List<Object>> data = createData(sheet);
			excelDtos.add(new ExcelDto(title, data, excelPath));
		}
		return excelDtos;
	}

	private static List<List<Object>> createData(Sheet sheet) {
		List<List<Object>> data = new ArrayList<List<Object>>();
		int colNum = sheet.getRow(0).getPhysicalNumberOfCells();
		Row row = null;
		for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			row = sheet.getRow(i);
			List<Object> rowData = new ArrayList<Object>();
			for (int j = 0; j < colNum; j++) {
				if (row.getCell(j) != null) {
					switch (row.getCell(j).getCellType()) {
					case Cell.CELL_TYPE_STRING:
						rowData.add(row.getCell(j).getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						rowData.add((int) (row.getCell(j).getNumericCellValue()));
						break;
					case Cell.CELL_TYPE_BOOLEAN:
						rowData.add((boolean) (row.getCell(j).getBooleanCellValue()));
						break;
					default:
						rowData.add("");
						break;
					}
				} else {
					rowData.add("");
				}

			}
			data.add(rowData);
		}
		return data;
	}

	private static List<String> createTitle(Sheet sheet) {
		Row row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();

		List<String> title = new ArrayList<String>();
		for (int i = 0; i < colNum; i++) {
			title.add(row.getCell(i).getStringCellValue());
		}
		return title;
	}

	private static Workbook createWorkbook(File excelFile) throws Exception {
		Workbook wb = null;
		String fileName = excelFile.getName();
		if (fileName.endsWith(XLSX_FILE_NAME) || fileName.endsWith(XLTX_FILE_NAME)) {
			wb = new XSSFWorkbook(new FileInputStream(excelFile));
		} else if (fileName.endsWith(XLS_FILE_NAME)) {
			wb = new HSSFWorkbook(new FileInputStream(excelFile));
		} else {
			throw new MetadataException("不支持的文件格式");
		}
		return wb;
	}

}
